/*
- Root category appears in Browse after initial selection. Why?
-put_product !!! Remaining bug in here <-. Missing right parenthesis?
*/

import java.sql.*;
import java.util.*;

public class Customer
{
    private Connection connection;
    private Statement statement;            
    private ResultSet resultSet;
    private String query;
    private Scanner input = new Scanner(System.in);
    private int auctionID;        // For putting up new products.
    private int bidSN;
    private String user;
    
    public Customer(Connection c, Statement s, String login)
    {
        connection = c;
        statement = s;
        user = login;
        ConsoleMenu();        
    }
    
    public void ConsoleMenu()
    {
        int swValue;
        boolean Exit;
        
        try
        {
            // replace auctionID and bidSN
            query = "SELECT AuctionID FROM PRODUCT ORDER BY AuctionID DESC";
            resultSet = statement.executeQuery(query);
            resultSet.next();
            auctionID = resultSet.getInt(1);
            
            query = "SELECT Bidsn FROM BIDLOG ORDER BY BidSN DESC";
            resultSet = statement.executeQuery(query);
            resultSet.next();
            bidSN = resultSet.getInt(1);
            
            connection.commit();
        }
        catch(Exception Ex)
        {
          System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
        }
        
        Exit = false;
        
        while(!Exit)
        {
            System.out.println("==========================================");
            System.out.println("|   CUSTOMERS                            |");
            System.out.println("==========================================");
            System.out.println("| Options:                               |");
            System.out.println("|        1. Browse Products              |");
            System.out.println("|        2. Search                       |");
            System.out.println("|        3. Auction an Item              |");
            System.out.println("|        4. Bid on an Item               |");
            System.out.println("|        5. Sell an Item                 |");
            System.out.println("|        6. Product Suggestions          |");
            System.out.println("|        7. Exit                         |");
            System.out.println("==========================================");
            swValue = Keyin.inInt(" Select option: ");

            switch(swValue)
            {
                case 1:
                    Browse();
                    break;
                case 2:
                    Search();
                    break;
                case 3:
                    Auction();
                    break;
                case 4:
                    Bid();
                    break;
                case 5:
                    Sell();
                    break;
                case 6:
                    Suggest();
                    break;
                case 7:
                    System.out.println("Exit selected");
                    Exit = true;
                    break;
                default:
                    System.out.println("Invalid selection");
                    break;
            }
            
            System.out.println("Hit enter to continue.");
            input.nextLine();
        }
    }
    
    public void Browse()
    {
        String catChoice = "";
        int sortChoice;
        boolean ready;
        String rsltLine;
        
        try
        {            
        
        System.out.println("Select a category to view items.");
        query = "SELECT Name FROM CATALOG WHERE ParentCatalog IS NULL";
        resultSet = statement.executeQuery(query);
        
        do
        {
            if(resultSet.next())
            {
                do
                {
                    System.out.println(resultSet.getString(1));
                }while(resultSet.next());
            }
            else
            {
                break;        // resultSet was empty, meaning this is a leaf category.    
            }    
        
            System.out.print("Enter category choice: ");
            catChoice = input.nextLine();
        
            query = "SELECT name FROM CATALOG WHERE ParentCatalog = '" + catChoice + "'";
            resultSet = statement.executeQuery(query);
        }while(true);
        
        // catChoice now holds the directory we want to list products for
        do
        {
            ready = true;
            System.out.println("Order products by...");
            System.out.println("    1. Highest Bid Amount");
            System.out.println("    2. Alphabetically by Name");
            System.out.println("Enter choice: ");
            sortChoice = input.nextInt();
        
            switch(sortChoice)
            {                                              
                case 1:
                // Amount contains highest bid, when available. 
                    query = "SELECT AuctionID, Name, Description, Amount FROM PRODUCT NATURAL JOIN BELONGSTO WHERE Catalog = '" + catChoice + "' ORDER BY Amount DESC";
                    break;
                case 2:
                    query = "SELECT AuctionID, Name, Description FROM PRODUCT NATURAL JOIN BELONGSTO WHERE Catalog = '" + catChoice + "' ORDER BY Name ASC";
                    break;
                default:
                    ready = false;
                    break;
            }
        }while(!ready);
        
        resultSet = statement.executeQuery(query);
        if(sortChoice == 1)
        {
            System.out.println("  AuctionID          Name        Description         Highest Bid");
            ////System.out.println("-------------     --------  ---------------  ---------------");
            while(resultSet.next())
            {
                rsltLine = Integer.toString(resultSet.getInt(1)) + "  \t  ";
                rsltLine = rsltLine + resultSet.getString(2) + "  \t  ";
                rsltLine = rsltLine + resultSet.getString(3) + "  \t  ";
                rsltLine = rsltLine + Integer.toString(resultSet.getInt(4));
                System.out.println(rsltLine);
            }
        }
        else
        {
            System.out.println("  AuctionID          Name        Description");
            ////System.out.println("-------------     --------  ---------------");
            while(resultSet.next())
            {
                rsltLine = Integer.toString(resultSet.getInt(1)) + "  \t  ";
                rsltLine = rsltLine + resultSet.getString(2) + "  \t  ";
                rsltLine = rsltLine + resultSet.getString(3);
                System.out.println(rsltLine);
            }
        }        
        
            connection.commit();
        }
        catch(Exception Ex)
        {
          System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
        }                                                
    }
    
    public void Search()
    {
        try
        {
            String searchString;
            String[] terms;
            boolean goodNum;        // Right number of search terms?
            String rsltLine;
            
            System.out.println("Search for a product by description.");
            
            do
            {
                System.out.println("Search Keywords (up to 2): ");
                searchString = input.nextLine();
            
                terms = searchString.split(" ");
                goodNum = (terms.length >= 1 && terms.length <= 2);
                if(!goodNum)
                {
                    System.out.println("Please enter 1 or 2 search terms.");
                }
            }while(!goodNum);
            
                if(terms.length == 2)
                {
                    query = "SELECT AuctionId, Name, Description FROM PRODUCT WHERE Description LIKE '%" + terms[0] + "%" + terms[1] + "%'";
                    query = query + "UNION SELECT AuctionId, Name, Description FROM PRODUCT WHERE Description LIKE '%" + terms[1] + "%" + terms[0] + "%'";
                }
                else
                {
                    query = "SELECT AuctionId, Name, Description FROM PRODUCT WHERE Description LIKE '%" + terms[0] + "%'";
                }

            resultSet = statement.executeQuery(query);
            
            System.out.println("Results");
            System.out.println("  AuctionID          Name          Description    ");
            ////System.out.println("-------------    --------    ---------------")'
            while(resultSet.next())
            {    
                rsltLine = Integer.toString(resultSet.getInt(1)) + "  \t  ";
                rsltLine = rsltLine + resultSet.getString(2) + "  \t  ";
                rsltLine = rsltLine + resultSet.getString(3);
                System.out.println(rsltLine);
            }
            
            connection.commit();
        }
        catch(Exception Ex)
        {
            System.out.println("Error." + Ex.toString());
        }
    }
    
    public void Auction()
    {
        String name;
        String description;
        String categories;    // to be parsed into an array
        String[] listCategories;
        int numDays;
        int minPrice;
        
        try
        {                
        
        auctionID++;
        
        System.out.println("Put a product up for auction.");
        System.out.println("Product Name: ");
        name = input.nextLine();
        System.out.println("Description (Optional): ");
        description = input.nextLine();
        System.out.println("Enter all categories this product belongs to(separated by ','): ");
        categories = input.nextLine();
        listCategories = categories.split(",");
        System.out.println("How many days should this product stay up for auction?: ");
        numDays = input.nextInt();
        System.out.println("What is the minimum selling price?: ");
        minPrice = input.nextInt();
                        
        /*
        query = "{CALL put_product(" + Integer.toString(auctionID) + ", ";
        query = query + name + ", " + user + ", " + Integer.toString(minPrice) + ", ";
        query = query + description + ", " + Integer.toString(numDays) + ")}";        
        statement.executeQuery(query);
		*/
        
        query = "{CALL put_product("+auctionID+", '"+name+"', '"+user+"', "+minPrice+", '"+description+"', "+numDays+")}";        
        CallableStatement stmt = connection.prepareCall(query);
        stmt.execute();
        
        for(int i = 0; i < listCategories.length; i++)
        {
            query = "SELECT * FROM CATALOG WHERE name = '" + listCategories[i] + "'";
            resultSet = statement.executeQuery(query);
            
            if(resultSet.next())
            {
                query = "SELECT * FROM CATALOG WHERE ParentCatalog = '" + listCategories[i] + "'";
                resultSet = statement.executeQuery(query);
                if(resultSet.next())
                {
                    System.out.println("Could not add product to category " + listCategories[i] + " because it is not a leaf category.");
                }
                else
                {    
                    query = "INSERT INTO BELONGSTO VALUES(" + auctionID + ", '" + listCategories[i] + "')";
                    statement.executeQuery(query);
                }
            }
            else
            {
                System.out.println("Could not add product to category " + listCategories[i] + " because it does not exist.");
            }
        }
        
            connection.commit();
        }
        catch(Exception Ex)
        {
          System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
        }                                                
    }
    
    public void Bid()
    {
        int id;
        int bidAmount;
        int highestBid;
        String stat;
        
        try
        {                
        
        System.out.println("Which item would you like to bid on? Enter AuctionID: ");
        id = input.nextInt();
        System.out.println("How much would you like to bid? : ");
        bidAmount = input.nextInt();
        
        query = "SELECT Amount, Status FROM PRODUCT WHERE AuctionID = " + Integer.toString(id);
        resultSet = statement.executeQuery(query);
        
        if(resultSet.next())
        {
            highestBid = resultSet.getInt(1);
            stat = resultSet.getString(2);
            
            if(stat.equalsIgnoreCase("UNDERAUCTION"))
            {
                if(bidAmount > highestBid)
                {
                    bidSN++;
                    query = "INSERT INTO BIDLOG VALUES(" + Integer.toString(bidSN) + ", ";
                    query = query + Integer.toString(id) + ", '" + user + "', ";
                    query = query + "(SELECT C_DATE FROM OURDATE), " + Integer.toString(bidAmount) + ")"; 
                    statement.executeQuery(query);
                    
                    query = "UPDATE PRODUCT SET Amount = " + Integer.toString(bidAmount);
                    query = query + "WHERE AuctionID = " + Integer.toString(id);
                    statement.executeQuery(query);
                }
                else
                {
                    System.out.println("Must bid higher - current price is " + Integer.toString(highestBid));
                }
            }
            else
            {
                System.out.println("Product is not up for auction.");
            }
        }
        else
            System.out.println("Product does not exist.");
        
            connection.commit();
        }
        catch(Exception Ex)
        {
          System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
        }                                                
    }
    
    public void Suggest()
    {
        ArrayList<String> BiddingFriends = new ArrayList<String>();
        ArrayList<Integer> Products = new ArrayList<Integer>();
        ArrayList<Integer> Desirabilities = new ArrayList<Integer>();
        ArrayList<String> Bidders = new ArrayList<String>();
        ArrayList<String> Names = new ArrayList<String>();
        int count;
        
        try
        {    
            query = "SELECT DISTINCT b2.Bidder "+
                    "FROM BIDLOG b1, BIDLOG b2 "+
                    "WHERE b1.Bidder='"+user+"'"+
                    " AND b1.AuctionID=b2.AuctionID"+
                    " AND b2.Bidder!='"+user+"'";
            resultSet = statement.executeQuery(query);
            while (resultSet.next())
            {
                BiddingFriends.add(resultSet.getString(1));
            }
            if (BiddingFriends.size()==0)
            {
                System.out.println(" You have no bidding friends.");
            }
            else
            {
                System.out.println("Your bidding friends: ");
                for (int i=0;i<BiddingFriends.size();i++)
                {
                    System.out.println(BiddingFriends.get(i));
                }            
                for (int i=0;i<BiddingFriends.size();i++)
                {
                    query = "SELECT DISTINCT b.AuctionID, p.Name "+
                            "FROM BIDLOG b, PRODUCT p "+
                            "WHERE b.Bidder='"+BiddingFriends.get(i)+"'"+
                            " AND b.AuctionID=p.AuctionID";
                    resultSet = statement.executeQuery(query);
                    while (resultSet.next())
                    {
                        if (!Products.contains(resultSet.getInt(1)))
                        {
                            Products.add(resultSet.getInt(1));
                            Names.add(resultSet.getString(2));
                        }
                    }
                }
                for (int i=0;i<Products.size();i++)
                {
                    query = "SELECT DISTINCT Bidder "+
                            "FROM BIDLOG "+
                            "WHERE AuctionID="+Products.get(i);
                    resultSet = statement.executeQuery(query);
                    count = 0;
                    while (resultSet.next())
                    {
                        if (BiddingFriends.contains(resultSet.getString(1)))
                        {
                            count++;
                        }
                    }
                    Desirabilities.add(count);
                }
                sort(Products, Names, Desirabilities);
                System.out.println("Suggestions: ");
                System.out.println("AuctionID\tName\tDesirability");
                for (int i=0;i<Products.size();i++)
                {
                    System.out.println(Products.get(i)+"\t"+Names.get(i)+"\t"+Desirabilities.get(i));
                }
            }
        
            connection.commit();
        }
        catch(Exception Ex)
        {
          System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
        }                                        
    }    
    
    public void sort(ArrayList<Integer> p, ArrayList<String> n, ArrayList<Integer> d)
    {
        int i;
        int j;
        String tempS;
        Integer tempI;
        
        for (i=0;i<d.size();i++)
            for (j=i+1;j<d.size();j++)
            if (d.get(i)<d.get(j))
            {
                tempI = p.get(i);
                p.set(i, p.get(j));
                p.set(j, tempI);
                tempS = n.get(i);
                n.set(i, n.get(j));
                n.set(j, tempS);
                tempI = d.get(i);
                d.set(i, d.get(j));
                d.set(j, tempI);
            }
    }
    
    public void Sell()
    {
        ArrayList<Integer> AuctionIDs = new ArrayList<Integer>();
        ArrayList<String> Names = new ArrayList<String>();
        int id;
        int sn;
        java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("dd-MMM-yy/hh:mm:ssa");
    
        try
        {    
            query = "SELECT AuctionID, Name FROM PRODUCT WHERE Seller='"+user+"' AND Status='closed'";
            resultSet = statement.executeQuery(query);
            while (resultSet.next())
            {
                AuctionIDs.add(resultSet.getInt(1));
                Names.add(resultSet.getString(2));
            }
            
            if (AuctionIDs.size()==0)
            {
                System.out.println(" You have no closed auctions.");
            }
            else
            {
                for (int i=0;i<AuctionIDs.size();i++)
                {
                    System.out.println(AuctionIDs.get(i)+"\t"+Names.get(i));
                }                    
                do
                {
                    id = Keyin.inInt(" Enter the auction id of the product you want to sell: ");
                }
                while (!AuctionIDs.contains(id));
                
                query = "SELECT Bidsn, Amount FROM BIDLOG WHERE AuctionID="+id+" ORDER BY Amount DESC";
                resultSet = statement.executeQuery(query);
                if (!resultSet.next())
                {
                    System.out.println(" No bidding for this product.");                    
                }
                else
                {
                    int amount;
                    
                    sn = resultSet.getInt(1);
                    amount = resultSet.getInt(2);
                                    
                    if (resultSet.next())
                    {
                        amount = resultSet.getInt(2);
                    }
                    query = "SELECT p.Name, p.Description, p.StartDate, p.MinPrice, p.NumberOfDays, b.Bidder, b.BidTime, b.Amount "+
                            "FROM PRODUCT p, BIDLOG b "+
                            "WHERE p.AuctionID="+id+
                            " AND b.Bidsn="+sn+
                            " FOR UPDATE";
                    resultSet = statement.executeQuery(query);
                    if (resultSet.next())
                    {
                        String buyer = resultSet.getString(6);
                        System.out.println(resultSet.getString(1)+"\t"+resultSet.getString(2)+"\t"+df.format(resultSet.getTimestamp(3))+"\t"+resultSet.getInt(4)+"\t"+resultSet.getInt(5)+"\t"+buyer+"\t"+df.format(resultSet.getTimestamp(7))+"\t"+amount);
                        String s = Keyin.inString(" Sell the product or withdraw the auction(s/w): ");
                        if (s.equals("s"))
                        {
                            query = "UPDATE PRODUCT "+
                                    "SET Status='sold', Buyer='"+buyer+"',"+
                                    " SellDate=(SELECT C_DATE FROM OURDATE), Amount="+amount+" "+
                                    "WHERE AuctionID="+id;
                            PreparedStatement updateStatement = connection.prepareStatement(query);
                            updateStatement.executeUpdate();
                            System.out.println(" The product is sold.");
                        }
                        else if (s.equals("w"))
                        {
                            query = "UPDATE PRODUCT "+
                                    "SET Status='withdrawn', amount=null "+
                                    "WHERE AuctionID="+id;
                            PreparedStatement updateStatement = connection.prepareStatement(query);
                            updateStatement.executeUpdate();    
                            System.out.println(" The product is withdrawn.");                                
                        }
                        else
                        {
                            System.out.println(" No action is performed.");
                        }
                    }
                }
            }
        
            connection.commit();
        }
        catch(Exception Ex)
        {
          System.out.println("Error running the sample queries.  Machine Error: "+Ex.toString());
        }                                                        
    }
    
    public static void main(String[] args)
    {
        String username, password;
        
        username = Keyin.inString(" Enter username: ");
        password = Keyin.inString(" Enter password: ");        
        
        String login = Keyin.inString(" Enter the customer's login: ");

        Connection c = ConnectionManager.getConnection(username, password);        
        Statement s = ConnectionManager.createStatement(c);    
        Customer customer = new Customer(c, s, login);
        ConnectionManager.closeConnection(c);        
    }
}
